const Sequelize = require('sequelize')
const conf = require('../config/mysql')

const sequelize = new Sequelize(conf.database, conf.user, conf.password, {
  host: conf.host,
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 30000
  },
  operatorsAliases: false
})

// 用户表映射对象
const Sysuser = sequelize.define(
  'sysuser',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    uname: Sequelize.STRING(50), // 用户名称
    upass: Sequelize.STRING(50), // 密码
    tname: Sequelize.STRING(50), // 姓名
    sex: Sequelize.STRING(50), // 性别
    tel: Sequelize.STRING(50), // 电话号码
    email: Sequelize.STRING(50), // email
    qq: Sequelize.STRING(50), // qq
    jg: Sequelize.STRING(50), // 籍贯
    addr: Sequelize.STRING(255), // 地址
    utype: Sequelize.STRING(50), // 用户类型
    delstatus: Sequelize.STRING(50), // 是否删除
    remark: Sequelize.STRING, // 旅行社用户简介
    status: Sequelize.STRING(50) // 是否在启用状态
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

//景区详情表映射对象
const Attractions = sequelize.define(
  'attractions',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //景点编号
    linename: Sequelize.STRING(50), // 景点名称
    province: Sequelize.STRING(50), // 所属地市
    areaid: Sequelize.STRING(50), // 所属地区
    img: Sequelize.STRING(255), // 景点图片
    price: Sequelize.STRING(50), // 门票全价
    remark: Sequelize.STRING, // 景点备注
    delstatus: Sequelize.STRING(50) // 空否删除
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

//库存记录表映射对象
const Kcrecord = sequelize.define(
  'kcrecord',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //库存编号
    pid: Sequelize.STRING(50), // 景点编号
    num: Sequelize.INTEGER(11), // 门票数量
    type: Sequelize.STRING(50) // 类型（入库或出库）
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

//留言表映射对象
const Message = sequelize.define(
  'message',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //留言编号
    title: Sequelize.STRING(50), // 留言标题
    content: Sequelize.STRING, // 留言内容
    tname: Sequelize.STRING(50), // 留言人姓名
    reply: Sequelize.STRING(50), // 留言回复
    savetime: Sequelize.STRING(50) // 留言时间
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

//新闻表映射对象
const News = sequelize.define(
  'news',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //新闻编号
    title: Sequelize.STRING(50), // 新闻标题
    content: Sequelize.STRING, // 新闻内容
    savetime: Sequelize.STRING(50), // 发布日期
    img: Sequelize.STRING(50), // 新闻图片
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

// 门票订单表映射对象
const Orders = sequelize.define(
  'ticketsorder',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },   //订单编号
    orderid: Sequelize.STRING(50), // 订单号
    pid: Sequelize.STRING(50), // 所属景点编号
    ptype: Sequelize.STRING(50), // 门票类型
    total: Sequelize.STRING(50), // 总价
    userid: Sequelize.STRING(50), // 购买账户编号
    adminid: Sequelize.STRING(50), // 售票员编号
    orderstatus: Sequelize.STRING(50), // 订单状态
    delstatus: Sequelize.STRING(50), // 是否已删除
    ptime: Sequelize.STRING(50), // 门票使用时间
    num: Sequelize.INTEGER(11), // 所购门票数量
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

// 地区表映射对象
const Address = sequelize.define(
  'area',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //地市编号
    areaname: Sequelize.STRING(100), //地区名称
    fatherid: Sequelize.STRING(50), // 地区编号
    delstatus: Sequelize.STRING(50) // 是否删除
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

// 城市表映射对象
const City = sequelize.define(
  'city',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //地市编号
    cityname: Sequelize.STRING(255), //城市名称
    delstatus: Sequelize.STRING(255)
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

// 轮播图表映射对象
const Carousel = sequelize.define(
  'carousel',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    }, //
    imgUrl: Sequelize.STRING(255), //img path
  },
  {
    timestamps: false,
    freezeTableName: true
  }
)

//景区表关联库存表
Attractions.hasOne(Kcrecord,{
	foreignKey: 'pid'
});

//景区表关联地区表
Attractions.belongsTo(City,{
	foreignKey: 'province'
});

//景区表关联地区表
Attractions.belongsTo(Address,{
	foreignKey: 'areaid'
});

//订单表关联用户表
Orders.belongsTo(Sysuser,{
	foreignKey: 'userid'
});

//订单表关联景区表
Orders.belongsTo(Attractions,{
	foreignKey: 'pid'
});

module.exports = {
  Sysuser,
  Orders,
  Address,
  City,
  News,
  Message,
  Kcrecord,
  Attractions,
  Carousel
}
